package org.txazo.zhenailight.script;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelLoader_11 {

	private static int workCityAmount = 0;
	private static int fileAmount = 0;
	private static int kpiAmount = 0;

	private static String startDate = null;
	private static String endDate = null;

	private static int[] kpiCellWidth = null;

	public static List<String> workCityCodeIds = new ArrayList<String>(50);
	public static Map<String, String> workCityCodeNames = new HashMap<String, String>(
			50);
	public static Map<String, String> workCityProvinceNames = new HashMap<String, String>(
			50);

	private static HSSFCell[][] cells = null;

	public static void autoGenerationExcelFromText(String excelTitle,
			String textDir, String[] textFileNames, String[] kpis, String ouput) {
		CityReader_11.readCityConf(ExcelLoader_11.class.getResource("")
				.getPath() + "city.conf");
		Collections.sort(workCityCodeIds);

		workCityAmount = workCityCodeIds.size();
		fileAmount = textFileNames.length;
		kpiAmount = kpis.length;

		kpiCellWidth = new int[kpiAmount];
		for (int i = 0; i < kpiAmount; i++) {
			kpiCellWidth[i] = kpis[i].length() * 2 + 4;
			if (kpiCellWidth[i] < 8) {
				kpiCellWidth[i] = 8;
			}
		}

		HSSFWorkbook workbook = new HSSFWorkbook();

		HSSFSheet sheet = workbook.createSheet(excelTitle);

		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 11);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

		HSSFCellStyle titleCellStyle = workbook.createCellStyle();
		titleCellStyle.setFont(font);
		titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

		HSSFRow row = null;

		Map<String, String[]> map = null;

		int rows = workCityAmount + 2;
		int cols = fileAmount * kpiAmount + 2;
		cells = new HSSFCell[rows][cols];
		for (int i = 0; i < rows; i++) {
			row = sheet.createRow(i);
			row.setHeight((short) (1.4 * 256));

			for (int j = 0; j < cols; j++) {
				cells[i][j] = row.createCell(j);
				if (i < 2) {
					cells[i][j].setCellStyle(titleCellStyle);
				} else {
					cells[i][j].setCellStyle(cellStyle);
				}
			}
		}

		for (int i = 0; i < cols; i++) {
			if (i == 0) {
				sheet.setColumnWidth(i, 16 * 256);
			} else if (i == 1) {
				sheet.setColumnWidth(i, 24 * 256);
			} else {
				int count = (i - 2) % kpiAmount;
				sheet.setColumnWidth(i, kpiCellWidth[count] * 256);
				if (kpiAmount == 1) {
					sheet.setColumnWidth(i, 20 * 256);
				}
			}
		}

		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
		cells[0][0].setCellValue("省份");

		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
		cells[0][1].setCellValue("城市");

		for (int i = 0; i < workCityAmount; i++) {
			cells[i + 2][0].setCellValue(workCityProvinceNames
					.get(workCityCodeIds.get(i)));
		}

		for (int i = 0; i < workCityAmount; i++) {
			cells[i + 2][1].setCellValue(workCityCodeNames.get(workCityCodeIds
					.get(i)));
		}

		for (int i = 0; i < fileAmount; i++) {
			sheet.addMergedRegion(new CellRangeAddress(0, 0, i * kpiAmount + 2,
					(i + 1) * kpiAmount + 1));

			String date = textFileNames[i].split("_")[1].split("\\.")[0];

			if (i == 0) {
				startDate = date;
			} else if (i == fileAmount - 1) {
				endDate = date;
			}

			date = convertDateString(date);

			cells[0][i * kpiAmount + 2].setCellValue(date);

			for (int k = 0; k < kpiAmount; k++) {
				cells[1][i * kpiAmount + k + 2].setCellValue(kpis[k]);
			}

			map = TextReader_11.readText(textDir + textFileNames[i], kpiAmount);

			for (int j = 0; j < workCityAmount; j++) {
				String[] kpiValues = map.get(workCityCodeIds.get(j));
				if (kpiValues != null) {
					for (int m = 0; m < kpiAmount; m++) {
						if (isEmpty(kpiValues[m])) {
							cells[j + 2][i * kpiAmount + m + 2]
									.setCellValue("0");
						} else {
							cells[j + 2][i * kpiAmount + m + 2]
									.setCellValue(Double.valueOf(kpiValues[m]));
						}
					}
				} else {
					for (int m = 0; m < kpiAmount; m++) {
						cells[j + 2][i * kpiAmount + m + 2].setCellValue("0");
					}
				}
			}
		}

		try {
			FileOutputStream fos = new FileOutputStream(ouput + excelTitle
					+ "_" + startDate + "-" + endDate + ".xls");
			workbook.write(fos);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static String convertDateString(String date) {
		StringBuilder sb = new StringBuilder(20);
		sb.append(date.substring(0, 4));
		sb.append("年");
		sb.append(filterZero(date.substring(4, 6)));
		sb.append("月");
		sb.append(filterZero(date.substring(6, 8)));
		sb.append("日");
		return sb.toString();
	}

	public static String filterZero(String text) {
		if (text.charAt(0) == '0') {
			return String.valueOf(text.charAt(1));
		}
		return text;
	}

	public static boolean isEmpty(String str) {
		return str == null || str.length() == 0;
	}

	public static void main(String[] args) {
		autoGenerationExcelFromText("试点城市个人中心来表态", "E:/script/", new String[] {
				"laibiaotai_20130719.tmp", "laibiaotai_20130720.tmp",
				"laibiaotai_20130721.tmp", "laibiaotai_20130722.tmp",
				"laibiaotai_20130723.tmp", "laibiaotai_20130724.tmp",
				"laibiaotai_20130725.tmp", "laibiaotai_20130726.tmp",
				"laibiaotai_20130727.tmp", "laibiaotai_20130728.tmp",
				"laibiaotai_20130729.tmp", "laibiaotai_20130730.tmp",
				"laibiaotai_20130731.tmp" },
				new String[] { "秋波数", "中立数", "跳过数" }, "E:/script/");
	}

}
